package com.vecspace.database.model;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.fasterxml.jackson.core.JsonProcessingException;
import org.apache.commons.lang3.ArrayUtils;

import com.vecspace.exception.HException;
import com.vecspace.utils.Assert;
import com.vecspace.utils.StringHelper;
import com.vecspace.utils.TJSON;



/**
 * 
 * @version: 1.0
 * @author: wanghua
 */
public class DataTable implements Cloneable{	
	private DataColumn[] columns;
	private List<DataRow> rows;
	
	private int pageIndex;
	private int pageSize;
	private int totalCount = 0;
	
	public DataTable(){
		columns = new DataColumn[0];
		rows = new ArrayList<DataRow>(128);
	}
	
	public DataTable(DataColumn[] columns){
		this.columns = columns.clone();
		rows = new ArrayList<DataRow>(128);
	}
	
	public DataTable(String[] columnNames) throws HException{
		Assert.throwz(columnNames != null, "构造DataTable失败，字段数组不能为空");
		columns = new DataColumn[columnNames.length];
		for(int i=0; i<columnNames.length; ++i){
			String name = columnNames[i];
			columns[i] = new DataColumn(name);
		}
		rows = new ArrayList<DataRow>(128);
	}
	
	public DataTable(String[] columnNames, int[] attrFieldSqlDataTypes) throws HException{
		Assert.throwz(columnNames != null, "构造DataTable失败，字段数组不能为空");
		Assert.throwz(attrFieldSqlDataTypes != null, "构造DataTable失败，字段类型数组不能为空");
		columns = new DataColumn[columnNames.length];
		for(int i=0; i<columnNames.length; ++i){
			String name = columnNames[i];
			int dataType = attrFieldSqlDataTypes[i];
			columns[i] = new DataColumn(name, dataType);
		}
		rows = new ArrayList<DataRow>(128);
	}
	
	public DataTable(ResultSet rs) throws Exception {
		
		if (rs == null) {
			columns = new DataColumn[0];
			rows = new ArrayList<DataRow>(0);
			return;
		}
		
		rows = new ArrayList<DataRow>(64);
		ResultSetMetaData metaData;

		metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		columns = new DataColumn[columnCount];
		for(int i=0; i<columnCount; ++i){
			columns[i] = new DataColumn();
		}
		int attrColumnIndex = -1;
		for(int i=0;i<columnCount;++i){
			String columnName = metaData.getColumnLabel(i+1);
			++attrColumnIndex;
			columns[i].setColumnName(columnName);
			columns[i].setDataType(metaData.getColumnType(i+1));					
		}  
		
		while(rs.next()){
			DataRow row = new DataRow(this, columnCount);
			attrColumnIndex = -1;
			for(int i=0;i<columnCount;++i){
				Object val = null;
				++attrColumnIndex;
				val = rs.getObject(i+1);
				row.setObject(attrColumnIndex,val);			
			}
			rows.add(row);
		}
	}
	
	/**在末尾增加一列*/
	public int addColumn(DataColumn column) {
		if(column != null) {
			int idx = this.findColumn(column.getColumnName());
			if(idx < 0) {
				this.columns = ArrayUtils.add(this.columns, column);
				for(DataRow row : this.rows) {
					row.appendColumn();
				}
				return this.columns.length - 1;
			}
		}
		return -1;
	}
	
	public void removeColumn(String columnName) {
		int idx = this.findColumn(columnName);
		removeColumn(idx);
	}
	
	public void removeColumn(int columnIndex) {
		if(columnIndex>=0 && columnIndex < this.columns.length) {
			this.columns = ArrayUtils.remove(this.columns, columnIndex);
			for(DataRow row : this.rows) {
				row.removeColumn(columnIndex);
			}
		}
	}
	
	public DataTable(List<Map<String,Object>> maplist) throws Exception {
		
		if (maplist == null || maplist.size() == 0) {
			columns = new DataColumn[0];
			rows = new ArrayList<DataRow>(0);
			return;
		}
		Map<String,Object> row0 = maplist.get(0);
		rows = new ArrayList<DataRow>(64);
	 
		int columnCount = row0.size();
		columns = new DataColumn[columnCount];
		for(int i=0; i<columnCount; ++i){
			columns[i] = new DataColumn();
		}
		int attrColumnIndex = -1;
		int colIndex = 0;
		for(String columnName : row0.keySet()){			 
			++attrColumnIndex;
			columns[colIndex].setColumnName(columnName);
			columns[colIndex].setDataType(0);	
			++colIndex;
		}  
		
		for(Map<String,Object> maprow : maplist){
			DataRow row = new DataRow(this, columnCount);
			attrColumnIndex = -1;
			for(int i=0;i<columnCount;++i){
				Object val = null;
				++attrColumnIndex;
				String columnName = columns[attrColumnIndex].getColumnName();
				val = maprow.get(columnName);
				row.setObject(attrColumnIndex,val);			
			}
			rows.add(row);
		}
	}
	
	/**
	 * 返回columnName所在位置的索引值 从0开始。不包含Geometry字段。
	 * @param columnName
	 * @return:int
	 */
	public int findColumn(String columnName){
		int len = columns.length;
		for(int i=0;i<len;++i){
			String fieldName2 = columns[i].getColumnName();
			if(fieldName2 != null && fieldName2.equalsIgnoreCase(columnName)){
				return i;
			}
		}
		return -1;
	}

	public DataColumn getColumn(int columnIndex){
		return columns[columnIndex];
	}
	
	public String getColumnName(int columnIndex){
		return columns[columnIndex].getColumnName();
	}
	
	public int getColumnType(int columnIndex){
		return columns[columnIndex].getDataType();
	}
	
	public int getRowCount(){
		return rows.size();
	}
	
	/**
	 * @param rowIndex 从0开始
	 * @return:DataRow
	 */
	public DataRow getRow(int rowIndex){
		return rows.get(rowIndex);
	}
	
	public DataColumn[] getColumns() {
		return this.columns;
	}
	
	/**
	 * 返回属性字段的个数（不包括Geometry字段）
	 * @return:int
	 */
	public int getColumnCount(){
		if(columns == null) {
			return 0;
		}
		return columns.length;
	}

	public List<Map<String, Object>> getRowList() {
		if(rows == null || rows.size()==0) {
			return null;
		}
		
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		int rowCount = rows.size();
		int colCount = this.getColumnCount();
		for (int rowIndex = 0; rowIndex < rowCount; ++rowIndex) {
			DataRow row = rows.get(rowIndex);
			Map<String,Object> map = new LinkedHashMap<String,Object>();
			for (int colIndex = 0; colIndex < colCount; ++colIndex) {
				String colName = this.columns[colIndex].getColumnName().toUpperCase();
				Object val = row.getObject(colIndex);
				map.put(colName, val);
			}			
			list.add(map);
		}
		return list;
	}
	

	public List<Map<String, Object>> getRowListLowerName() {
		if(rows == null || rows.size()==0) {
			return null;
		}
		
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		int rowCount = rows.size();
		int colCount = this.getColumnCount();
		for (int rowIndex = 0; rowIndex < rowCount; ++rowIndex) {
			DataRow row = rows.get(rowIndex);
			Map<String,Object> map = new LinkedHashMap<String,Object>();
			for (int colIndex = 0; colIndex < colCount; ++colIndex) {
				String colName = this.columns[colIndex].getColumnName().toLowerCase();
				Object val = row.getObject(colIndex);
				map.put(colName, val);
			}			
			list.add(map);
		}
		return list;
	}
	
	public String toJsonString() throws JsonProcessingException {
		if(rows == null || rows.size()==0) {
			return "";
		}
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		int rowCount = rows.size();
		int colCount = this.getColumnCount();
		for (int rowIndex = 0; rowIndex < rowCount; ++rowIndex) {
			DataRow row = rows.get(rowIndex);
			Map<String,Object> map = new LinkedHashMap<String,Object>();
			for (int colIndex = 0; colIndex < colCount; ++colIndex) {
				String colName = this.columns[colIndex].getColumnName();
				Object val = row.getObject(colIndex);
				map.put(colName, val);
			}
			list.add(map);
		}
		return TJSON.toJSONString(list);
	}
	
	public void print(){
		String str = "";
		for(int i=0; i<this.columns.length; ++i){
			str += this.columns[i].getColumnName() + "\t";
		}
		System.out.println(str);
		
		int rowCount = this.getRowCount();
		for(int j=0; j<rowCount; ++j){
			DataRow row = this.getRow(j);
			str = "";
			for(int i=0; i<this.columns.length; ++i){
				Object val = row.getObject(i);
				String s = "null";
				if(val != null) {
					s = val.toString();
				}
				str += s + "\t";
			}
			System.out.println(str);
		}		
	}
	/**
	 * 判断某一列是否为数字类型
	 * @param columnIndex
	 * @return 
	 * @author: wanghua
	 */
	public boolean isNumbericColumn(int columnIndex){
		if(this.columns == null) {
			return false;
		}
		return isNumbericType(columns[columnIndex].getDataType());
	}
	
	private boolean isNumbericType(int colType) {
		boolean isBoolean = false;
		if (colType == java.sql.Types.BIGINT|| colType == java.sql.Types.DOUBLE
				|| colType == java.sql.Types.FLOAT || colType == java.sql.Types.NUMERIC
				|| colType == java.sql.Types.SMALLINT || colType == java.sql.Types.INTEGER
				|| colType == java.sql.Types.DECIMAL
				|| colType == java.sql.Types.TINYINT) {
			isBoolean = true;
		}
		return isBoolean;
	}

	public int getPageIndex() {
		return pageIndex;
	}

	public void setPageIndex(int pageIndex) {
		this.pageIndex = pageIndex;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}
	
	/**创建一个row，但是不加到table rows中
	 * 
	 * @return 
	 * @author: wanghua
	 */
	public DataRow newRow(){
		DataRow row = new DataRow(this, this.getColumnCount());
		return row;
	}
	
	public void addRow(DataRow row){
		this.rows.add(row);
		++this.totalCount;
	}
	
	public void removeRow(int rowIndex) {
		this.rows.remove(rowIndex);
		--this.totalCount;
	}
		
	/**
	 * 两个table左联接 
	 * this left join otherTable
	 * @param otherTable
	 * @param joinColumns 本表的连接字段
	 * @param otherJoinColumns 连接字段（other table）
	 * @return 
	 * @throws HException 	 
	 * @author: wanghua
	 */
	public DataTable leftJoin(DataTable otherTable,String[] joinColumns, String[] otherJoinColumns) throws HException{
		Assert.throwz(joinColumns != null && joinColumns.length > 0, "关联字段不能为空");
		Assert.throwz(otherJoinColumns != null && otherJoinColumns.length > 0, "关联字段不能为空");

		DataColumn[] newColumns = ArrayUtils.addAll(this.columns, otherTable.columns);
		for(int i=0; i<newColumns.length; ++i){
			DataColumn newColumn = newColumns[i];
			DataColumn newColumn2 = newColumn.clone();			 
			newColumns[i] = newColumn2;
		}
		//对同名字段进行处理
		for(int i=0; i<newColumns.length; ++i){
			DataColumn newColumn = newColumns[i];
			String columnName = newColumn.getColumnName();
			String newName = columnName;
			for(int j=i+1; j<newColumns.length;++j){
				String columnName2 = newColumns[j].getColumnName();
				if(columnName.equalsIgnoreCase(columnName2)){
					newName = StringHelper.incString(newName);
					newColumns[j].setColumnName(newName);
				}
			}
		}
		DataTable newTable = new DataTable(newColumns);

		if(rows == null) {
			return newTable;
		}

		Map<JoinData,List<DataRow>> otherDataMap = otherTable.buildJoinDataMap(otherJoinColumns);
		
		int[] columnIndexArr = new int[joinColumns.length];
		int joinColumnsCount = joinColumns.length;
		for(int i=0; i<joinColumnsCount; ++i){
			String columnName = joinColumns[i];
			int colIndex = this.findColumn(columnName);
			Assert.throwz(colIndex >= 0, "DataTable中不存在字段："+columnName);
			columnIndexArr[i] = colIndex; 
		}
		for(DataRow row : rows){
			String[] joinDataValue = new String[joinColumnsCount];
			for(int i=0;i<joinColumnsCount;++i){
				Object val = row.getObject(columnIndexArr[i]);
				if(val != null) {
					joinDataValue[i] =  val.toString();
				}
			}
			JoinData joinData = new JoinData(joinDataValue);
			
			//在otherTable中查找相同joinData的DataRow
			Object[] arr1 = row.getNativeData();
			List<DataRow> rows2 = otherDataMap.get(joinData);
			if(rows2 != null && rows2.size() > 0){
				//添加一条数据，由两个表的数据构成
				for(DataRow row2 : rows2){
					Object[] arr2 = row2.getNativeData();
					Object[] newArr = ArrayUtils.addAll(arr1, arr2);
					DataRow newRow = newTable.newRow();
					newRow.setData(newArr);
					newTable.addRow(newRow);
				}
			}else{
				//添加一条数据，由左表数据构成
				DataRow newRow = newTable.newRow();
				newRow.setData(arr1);
				newTable.addRow(newRow);
			}
		} 
		return newTable;
	}
	
	private Map<JoinData,List<DataRow>> buildJoinDataMap(String[] joinColumns) throws HException{
		Map<JoinData,List<DataRow>> dataMap = new LinkedHashMap<JoinData,List<DataRow>>();
		int[] columnIndexArr = new int[joinColumns.length];
		int joinColumnsCount = joinColumns.length;
		for(int i=0; i<joinColumnsCount; ++i){
			String columnName = joinColumns[i];
			int colIndex = this.findColumn(columnName);
			Assert.throwz(colIndex >= 0, "DataTable中不存在字段："+columnName);
			columnIndexArr[i] = colIndex; 
		}
		if(rows == null) {
			return dataMap;
		}
		for(DataRow row : rows){
			String[] joinDataValue = new String[joinColumnsCount];
			for(int i=0;i<joinColumnsCount;++i){
				Object val = row.getObject(columnIndexArr[i]);
				if(val != null) {
					joinDataValue[i] =  val.toString();
				}
			}
			JoinData joinData = new JoinData(joinDataValue);
			List<DataRow> rows2 = dataMap.get(joinData);
			if(rows2 == null){
				rows2 = new ArrayList<DataRow>();
				dataMap.put(joinData, rows2);
			}
			rows2.add(row);
		}
		return dataMap;
	}
	
	/**对指定列进行转置（行转列）
	 * @param transposeColumnName 要转置的字段（只能对一个字段转置）
	 * @param floatColumns 转置后的二级字段(一般为数值指标字段)
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	public DataTable transpose(String transposeColumnName, String[] floatColumns) throws Exception{
		Map<String,Integer> transposeValues = new LinkedHashMap<>();
		
		if(floatColumns == null || floatColumns.length == 0) {
			throw new Exception("必须指定转置二级指标字段");
		}
		
		if(this.rows == null || this.rows.size() == 0 ) {
			return this;
		}
		
		int transposeColumnIndex = this.findColumn(transposeColumnName);
		if(transposeColumnIndex < 0) {
			throw new Exception("字段不存在:" + transposeColumnName);
		}
		int transposeValueIndex = 0;
		for(DataRow row : rows){ //获取转置字段的值的集合
			Object obj = row.getObject(transposeColumnIndex);
			if(obj == null || obj.toString().length() == 0){
				obj = "NULL";
			}
			if(!transposeValues.containsKey(obj)){				 
				transposeValues.put(obj.toString(),++transposeValueIndex);			 
			}
		}
		

		int[] columnTransposeFlag = new int[columns.length];
		for(int i=0; i< columns.length; ++i){
			String columnName = columns[i].getColumnName();
			if(transposeColumnName.equalsIgnoreCase(columnName)){
				columnTransposeFlag[i] = 1;//转置字段
			}else{
				boolean isFloatColumn = false;
				for(String floatColumnName : floatColumns){
					if(columnName.equalsIgnoreCase(floatColumnName)){
						isFloatColumn = true;
						break;
					}
				}
				if(isFloatColumn){
					columnTransposeFlag[i] = 2;//转置二级字段（浮动字段）
				}else{
					columnTransposeFlag[i] = 3;//固定字段
				}
			}
		}
		
		List<DataColumn> newColumnList = new ArrayList<>();
		for(int i=0; i< columns.length; ++i){
			if(columnTransposeFlag[i] == 3){ //固定字段
				newColumnList.add(columns[i].clone());
			}
		}
		
		for(Object transposeValue : transposeValues.keySet()){
			//int transposeValueIndex2 =  transposeValues.get(transposeValue);
			for(int i=0; i< columns.length; ++i){
				if(columnTransposeFlag[i] == 2){//转置二级字段（浮动字段）
					DataColumn newColumn = columns[i].clone();
					newColumn.setSourceColumnName(columns[i].getColumnName());
					newColumn.setColumnName(newColumn.getColumnName()+"_"+transposeValue);
					newColumn.setColumnCaption(transposeValue + "|" + newColumn.getDisplayCaption() );
					newColumnList.add(newColumn);
				}
			}
		}
		
		DataTable newDataTable = new DataTable(newColumnList.toArray(new DataColumn[newColumnList.size()]));
		Map<String, DataRow> newRowMap = new LinkedHashMap<String, DataRow>();// <固定字段值, newDataTable.DataRow>
		int oldTableRowsCount = this.rows.size();
		StringBuilder tmpStr = new StringBuilder();
		Map<String,Integer> newColumnNameIndex = new LinkedHashMap<>();//newDataTable，<ColumnName, ColumnIndex>
		for(int i=0; i<newDataTable.getColumnCount(); ++i){
			String name = newDataTable.columns[i].getColumnName();
			newColumnNameIndex.put(name.toUpperCase(), i);
		}
		for(int i=0; i< oldTableRowsCount; ++ i){
			DataRow oldRow = this.rows.get(i);
			tmpStr.delete(0, tmpStr.length());
			String transposeValue = "";
			for(int j=0; j< columns.length; ++j){
				if(columnTransposeFlag[j] == 3){ //固定字段
					Object cellValue = oldRow.getObject(j);
					String cellStr = cellValue == null ? "" : cellValue.toString();
					tmpStr.append(cellStr).append("#|#");
				}else if(columnTransposeFlag[j] == 1){//转置字段
					Object cellValue = oldRow.getObject(j);
					transposeValue = cellValue == null ? "" : cellValue.toString();
					
				}
			}
			String rowKey = tmpStr.toString();
			if(rowKey == null || rowKey.length() == 0) {
				rowKey = "NULL";
			}
			if(transposeValue == null || transposeValue.length() == 0){
				transposeValue = "NULL";
			}
			
			DataRow newRow;
			if(newRowMap.containsKey(rowKey)){
				newRow = newRowMap.get(rowKey);
			}else{
				newRow = newDataTable.newRow(); //生成一个新行
				newDataTable.addRow(newRow);
				//将固定字段填充到新行中
				for(int j=0; j< columns.length; ++j){ 
					if(columnTransposeFlag[j] == 3){ //固定字段
						Object cellValue = oldRow.getObject(j);
						String newColumnName = columns[j].getColumnName();
						int columnIndex = newColumnNameIndex.get(newColumnName.toUpperCase());
						newRow.setObject(columnIndex, cellValue);
					}
				}
				newRowMap.put(rowKey, newRow);
			}
			//将浮动字段值填充到新行中
			for(int j=0; j< columns.length; ++j){
				if(columnTransposeFlag[j] == 2){ //转置二级字段（浮动字段）
					Object cellValue = oldRow.getObject(j);
					String newColumnName = columns[j].getColumnName() + "_" + transposeValue;
					int columnIndex = newColumnNameIndex.get(newColumnName.toUpperCase());
					newRow.setObject(columnIndex, cellValue);
				}
			}
		}
		return newDataTable;
	}
	
	/**对指定列进行转置（行转列）
	 * @param transposeColumnName 要转置的字段（只能对一个字段转置）
	 * @param dimensionColumns 维度字段（固定字段）
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	public DataTable transpose2(String transposeColumnName, String[] dimensionColumns) throws Exception{
		if(dimensionColumns == null) {
			dimensionColumns = new String[0];
		}
		List<String> floatColumns = new ArrayList<>();
		
		for(int i=0; i<columns.length; ++i){
			String colName = columns[i].getColumnName();
			if(!colName.equalsIgnoreCase(transposeColumnName)){
				boolean isDimensionColumn = false;
				for(int j=0; j<dimensionColumns.length; ++j){
					String dimonColName = dimensionColumns[j];
					if(colName.equalsIgnoreCase(dimonColName)){
						isDimensionColumn = true;
						break;
					}
				}
				if(!isDimensionColumn){
					floatColumns.add(colName);
				}
			}
		}
		return transpose(transposeColumnName, floatColumns.toArray(new String[floatColumns.size()]));
	}
	
	public List<Map<String,Object>> toMapList(){
		if(this.rows == null || this.rows.size() == 0) {
			return null;
		}
		List<Map<String,Object>> mapRows = new ArrayList<>();
		for(DataRow row : this.rows){
			Map<String,Object> maprow = new LinkedHashMap<>();
			for(int i=0;i<columns.length;++i){
				Object val = row.getObject(i);
				String columnName = columns[i].getColumnName();
				maprow.put(columnName, val);
			}
			mapRows.add(maprow);
		}
		return mapRows;
	}
	
	@Override	
	public DataTable clone() throws CloneNotSupportedException {
		DataTable newTable = null;
		newTable = (DataTable)super.clone();	
		newTable.columns = ArrayUtils.clone(this.columns);
		newTable.rows = new ArrayList<>(this.rows.size());
		for(DataRow row : rows) {
			DataRow newRow = new DataRow(newTable, this.columns.length);
			for(int i=0; i<columns.length; ++i) {
				newRow.setObject(i, row.getObject(i));
			}
			newTable.addRow(newRow);
		}
		return newTable;
	}
}

